﻿using System;
using System.Collections.Generic;
using System.Text;
using Maticsoft.DBUtility;
using System.Data;
using System.Data.SqlClient;//请先添加引用
namespace ExtendBLL
{
    public class Arrangement
    {
        /// <summary>
        /// 获取课程安排 依据课程ID
        /// </summary>
        /// <param name="couID"></param>
        /// <returns></returns>
        public DataSet GetList(int couID)
        {
            string sql = "select distinct CourseID,LocationID,LocationName,[WeekDay],Lession from Arrangement where LocationID is not null and [WeekDay] is not null and Lession is not null and CourseID="+couID+" order by [WeekDay] asc";

            return DbHelperSQL.Query(sql);
        }

        /// <summary>
        /// 删除课程安排
        /// </summary>
        /// <param name="strWhere"></param>
        public void DeleteCouArr(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from Arrangement where ");
            strSql.Append(strWhere);

            DbHelperSQL.ExecuteSql(strSql.ToString());
        }

        /// <summary>
        /// 获取所有课程安排
        /// </summary>
        /// <returns></returns>
        public DataSet GetAllArrangement(int CourseID,string Week,string WeekDay,string LocationName,string TeacherName)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select distinct [Week] + ',' + cast([WeekDay] as varchar(5)) + ',' + cast(Lession as varchar(5)) + ',' + cast(LocationID as varchar(50)) as [ID], CourseID,CourseName,[Week],[WeekDay],Lession,LocationID,LocationName from Arrangement where [Week] is not null and [WeekDay] is not null and Lession is not null and LocationID is not null and CourseID=" + CourseID);
            if (Week != "0")
            {
                strSql.Append(" and [Week]='" + Week + "'");
            }
            if (WeekDay != "0")
            {
                strSql.Append(" and [WeekDay]=" +  WeekDay);
            }
            if (LocationName != "")
            {
                strSql.Append(" and LocationName like '%" + LocationName + "%'");

            }
            if (TeacherName != "")
            {
                strSql.Append(" and TeacherName like '%" + TeacherName + "%'");
            }
            strSql.Append(" order by CourseID,[Week],[WeekDay],Lession asc");

            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取老师信息
        /// </summary>
        /// <param name="Week"></param>
        /// <param name="WeekDay"></param>
        /// <param name="Lession"></param>
        /// <param name="LocationID"></param>
        /// <returns></returns>
        public DataSet GetTeaInfo(string Week, int WeekDay, int Lession, int LocationID)
        {
            string sql = "select * from Teacher where [ID] in (select distinct TeacherID from Arrangement where [Week]=" + Week + " and [WeekDay]=" + WeekDay + " and Lession=" + Lession + " and LocationID=" + LocationID + ")";

            return DbHelperSQL.Query(sql);
        }

        public DataSet GetCourseIDByTeaMisID(int ID)
        {
            string sql = "select Course.[ID] from TeachMission left join Course on TeachMission.Ms_CourseCode=Course.Course_Code where TeachMission.[ID]=" + ID;
            return DbHelperSQL.Query(sql);
        }

        public DataSet GetTeaMisID(int CourseID, string Year, string Term)
        {
            string sql = "select [ID] from TeachMission where Ms_CourseCode in (select Course_Code from Course where [ID]=" + CourseID + ") and Ms_Year='" + Year + "' and Ms_Term='" + Term + "'";
            return DbHelperSQL.Query(sql);
        }

    }


}
